package com.allinpay.dao;



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
/**
 * @author : wushikai
 * <p>
 * date : 2020-09-01
 */

public class ShiroDAO {

    public ShiroDAO() {
        try {
            Class.forName("com.mysql.jdbc.Driver");

        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public Connection getConnect()  throws SQLException{
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jbootdemo?characterEncoding=UTF-8", "root", "");
    }


	/**
	 1. getPassword 方法：
		根据用户名查询密码，这样既能判断用户是否存在，也能判断密码是否正确
  		String sql = "select password from user where name = ?";
	 */
    public String getPassword(String username) {
        String sql="SELECT password  FROM sys_user WHERE name=?";
        try (Connection connection = getConnect(); PreparedStatement preparedStatement = connection.prepareStatement(sql);){
            preparedStatement.setString(1, username);
            ResultSet resultSet=  preparedStatement.executeQuery();
            if(resultSet.next()) {
                return resultSet.getString("password");
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }
    /**
     2. listRoles 方法：
        根据用户名查询此用户有哪些角色，这是3张表的关联
         String sql = "select r.name from user u "
        + "left join user_role ur on u.id = ur.uid "
        + "left join Role r on r.id = ur.rid "
        + "where u.name = ?";
     */
    /**
     2. listRoles 方法：
        根据用户名查询此用户有哪些角色，这是3张表的关联
         String sql = "select r.name from user u "
        + "left join user_role ur on u.id = ur.uid "
        + "left join Role r on r.id = ur.rid "
        + "where u.name = ?";
     */
    public Set<String> listRoles(String username){
        Set<String> roles= new HashSet<>();
        String sql = "select r.name from user u "
                + "left join user_role ur on u.id = ur.uid "
                + "left join Role r on r.id = ur.rid "
                + "where u.name = ?";

        try (Connection connection = getConnect(); PreparedStatement preparedStatement = connection.prepareStatement(sql);){
            preparedStatement.setString(1, username);
            ResultSet resultSet=  preparedStatement.executeQuery();
            while(resultSet.next()) {
                roles.add(resultSet.getString(1));
            }

        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return roles;
    }
    /*
     3. listPermissions 方法：
        根据用户名查询此用户有哪些权限，这是5张表的关联

     */
    public Set<String> listPermissions(String username){
        Set<String> permissions = new HashSet<>();
        String sql =
                "select p.name from user u "+
                        "left join user_role ru on u.id = ru.uid "+
                        "left join role r on r.id = ru.rid "+
                        "left join role_permission rp on r.id = rp.rid "+
                        "left join permission p on p.id = rp.pid "+
                        "where u.name =?";

        try (Connection connection = getConnect(); PreparedStatement preparedStatement = connection.prepareStatement(sql);){
            preparedStatement.setString(1, username);
            ResultSet resultSet=  preparedStatement.executeQuery();
            while(resultSet.next()) {
                permissions.add(resultSet.getString(1));
            }

        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return permissions;
    }


    public static void main(String[] args) {
        System.out.println(new ShiroDAO().listRoles("zhang3"));
        System.out.println(new ShiroDAO().listRoles("li4"));
        System.out.println(new ShiroDAO().listPermissions("zhang3"));
        System.out.println(new ShiroDAO().listPermissions("li4"));
    }
}